/*
 * Copyright (C) 2007 James Deng (jtdeng@gmail.com)
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 *
 */


whenever sqlerror exit sql.sqlcode
prompt 'Creating package body plmsn_session...'


create or replace package body plmsn_session
as
	---------------------------------------------------------------
	-- Private member variables
	---------------------------------------------------------------
	m_http_proxy		varchar2(1024)	:= utl_config.get_cfg('http_proxy');
	m_wallet_path		varchar2(1024)	:= utl_config.get_cfg('wallet_path');
	m_wallet_password	varchar2(1024)	:= utl_config.get_cfg('wallet_password');
	m_initial_status	varchar2(4)		:= utl_config.get_cfg('initial_status');
	--m_character_set		varchar2(32)	:= utl_config.get_cfg('character_set');
	m_conn2notify		utl_connection.connection;
	m_passport			varchar2(64);
	m_password			varchar2(256);
	m_logged_on			boolean := false;
	m_contacts_version	pls_integer := 0;
	m_owner_id			pls_integer := 0;
	m_last_response		pls_integer;	--the time in 1/100 second at the point the client get last server response
	m_max_ping_retry	pls_integer := 3; --force logging off after continuous this much failures
	m_crlf				varchar2(10) := utl_connection.CRLF;
		
	---------------------------------------------------------------
	-- Private member procedures and functions
	---------------------------------------------------------------
	function send_cmd_sync(cmd IN varchar2, args IN varchar2) return varchar2
	is
		l_cmd	varchar2(1024);
		l_resp	varchar2(32767);
		l_rc	pls_integer;
		l_trid	pls_integer;
	begin
		select SEQ_TRANSACTION_ID.nextval into l_trid from dual;
		l_cmd := cmd || ' ' || l_trid || ' ' || args;
		l_rc := utl_connection.write_line(m_conn2notify, l_cmd);
		utl_logging.debug('package::plmsn_session.send_cmd_sync', 'SEND: ' || l_cmd);
		l_resp := utl_connection.get_line(m_conn2notify, TRUE);		
		utl_logging.debug('package::plmsn_session.send_cmd_sync', 'RECV: ' || l_resp);		
		return l_resp;
	exception
		when others then
			utl_logging.error('package::plmsn_session.send_cmd_sync', '[COMMAND]: ' || l_cmd || m_crlf || SQLERRM || m_crlf || ' [TRACE]: ' || DBMS_UTILITY.format_error_backtrace);
	end;
	
	function send_cmd_async(cmd IN varchar2, args IN varchar2, payload IN varchar2 default null) return pls_integer
	is
		l_cmd	varchar2(1024);
		l_rc	pls_integer;
		l_trid	pls_integer;
	begin
		if m_conn2notify.private_sd is null then
			raise_application_error(-20001, 'jesus, m_conn2notify.private_sd is null!');
		end if;
		select SEQ_TRANSACTION_ID.nextval into l_trid from dual;
		if payload is null
		then
			l_cmd := cmd || ' ' || l_trid || ' ' || args;
			l_rc := utl_connection.write_line(m_conn2notify, l_cmd);		
		else
			l_cmd := cmd || ' ' || l_trid || ' ' || args || m_crlf || payload;
			l_rc := utl_connection.write_text(m_conn2notify, l_cmd);
		end if;
		
		utl_logging.debug('package::plmsn_session.send_cmd_async', 'SEND: ' || l_cmd);
		return l_trid;
	exception
		when others then
			utl_logging.warning('package::plmsn_session.send_cmd_async', '[COMMAND]: ' || l_cmd || m_crlf || SQLERRM || m_crlf ||' [TRACE]: ' || DBMS_UTILITY.format_error_backtrace);
	end;

	function get_tweener return varchar2
	is
		l_host varchar2(256) := dispatch_host;
		l_port pls_integer := dispatch_port;
		l_resp_text varchar2(32767);
		l_resp_tab STRING_ARRAY := STRING_ARRAY();
		l_tweener varchar2(32767);
	begin
		loop
			m_conn2notify := utl_connection.open_connection(l_host, l_port, m_http_proxy);
			
			--VER
			l_resp_text := send_cmd_sync('VER', 'MSNP8 CVR0');			
			l_resp_tab := utl_string.split(l_resp_text);
	    	if l_resp_tab(1) <> 'VER' then
	    	   raise_application_error(-20001, 'Invalid Client Protocol Version: ' || l_resp_text);	    	
	    	end if;
			l_resp_tab.DELETE;
	    	
			--CVR
			l_resp_text := send_cmd_sync('CVR', '0x0804 win 5.10 i386 MSNMSGR 7.0.0816 PL/MSN ' || m_passport);

			--USR
			l_resp_text := send_cmd_sync('USR', 'TWN I ' || m_passport);
			l_resp_tab := utl_string.split(l_resp_text);
			if l_resp_tab(1) = 'XFR' then
				utl_logging.debug('package::plmsn_session.get_tweener', 'Redirecting to ' || l_resp_tab(4) );
				l_host := utl_string.split(l_resp_tab(4),':')(1);
				l_port := utl_string.split(l_resp_tab(4),':')(2);
				utl_connection.close_connection(m_conn2notify);
				l_resp_tab.DELETE;
	   		elsif l_resp_tab(1) = 'USR' then
				l_tweener := l_resp_tab(5);
				l_resp_tab.DELETE;
				exit;
			end if;						
		end loop;
		
		utl_logging.debug('package::plmsn_session.get_tweener', 'Got tweener: ' || l_tweener );
		return l_tweener;
	end;


	function get_ticket(tweener IN varchar2) return varchar2
	is
		l_req		UTL_HTTP.REQ;
		l_resp		UTL_HTTP.RESP;
		l_name		varchar2(256);
		l_value		varchar2(1024);
		l_passporturls	varchar2(1024);
		l_Authentication_Info	varchar2(32767);
		l_location	varchar2(1024);
		l_dalogin	varchar2(1024);
		l_ticket	varchar2(32767);
	begin
		if m_http_proxy is not NULL then
			UTL_HTTP.SET_PROXY(m_http_proxy);
		end if;
	
		UTL_HTTP.SET_WALLET(m_wallet_path, m_wallet_password);
		l_req := UTL_HTTP.BEGIN_REQUEST('https://nexus.passport.com/rdr/pprdr.asp');
		UTL_HTTP.SET_HEADER(l_req, 'User-Agent', 'Mozilla/4.0');
		l_resp := UTL_HTTP.GET_RESPONSE(l_req);

		if l_resp.status_code <> 200 then
			raise_application_error(-20002, 'Can not get passport logon host from https://nexus.passport.com');
		end if;
		
		utl_http.get_header_by_name(l_resp, 'PassportURLs', l_passporturls);
		UTL_HTTP.END_RESPONSE(l_resp);
			
		--Let's do it in a SQL way!
		select 'https://'||replace(column_value,'DALogin=','') into l_dalogin
		from table( utl_string.split(l_passporturls, ',') )
		where column_value like 'DALogin=%';

		utl_logging.debug('package::plmsn_session.get_ticket', 'DALogin: ' || l_dalogin );
	
		loop
			l_req := UTL_HTTP.BEGIN_REQUEST(l_dalogin);
			UTL_HTTP.SET_HEADER(l_req, 'Authorization', 'Passport1.4 OrgVerb=GET,OrgURL=http://messenger.msn.com,sign-in=' || m_passport || ',pwd=' || m_password || ',' || tweener);	
			l_resp := UTL_HTTP.GET_RESPONSE(l_req);
			
			declare
				l_resp_text varchar2(32767);
				l_header_name varchar2(256);
				l_header_value varchar2(1024);
			begin
				utl_logging.debug('package::plmsn_session.get_ticket', 'HttpStatusCode: ' || l_resp.status_code );
				
				for i in 1..UTL_HTTP.GET_HEADER_COUNT(l_resp)
				loop
					UTL_HTTP.GET_HEADER(l_resp, i, l_header_name, l_header_value);
					utl_logging.debug('package::plmsn_session.get_ticket', 'HttpResponseHeader: ' || l_header_name || ': ' || l_header_value );
				end loop;
			
				UTL_HTTP.READ_TEXT(l_resp, l_resp_text);			
				utl_logging.debug('package::plmsn_session.get_ticket', 'HttpResponseBody: ' || l_resp_text );
			exception
				when utl_http.end_of_body then
					null;
			end;
			
			if l_resp.status_code <> 200 then
				utl_http.get_header_by_name(l_resp, 'Location', l_location);
				l_dalogin := regexp_replace(l_location, '\?.*', '');
				utl_logging.debug('package::plmsn_session.get_ticket', 'Redirecting to: ' || l_dalogin );
				UTL_HTTP.END_RESPONSE(l_resp);
			else   
				utl_http.get_header_by_name(l_resp, 'Authentication-Info', l_authentication_Info);
				
				select trim( '''' from replace(column_value,'from-PP=','') ) into l_ticket
				from table( utl_string.split(l_authentication_Info,',') )
				where column_value like 'from-PP=%';
				UTL_HTTP.END_RESPONSE(l_resp);
				exit;
			end if;
		end loop;
	
		utl_logging.debug('package::plmsn_session.get_ticket', 'Got ticket: ' || l_ticket );
		return l_ticket;
	end;

	
	---------------------------------------------------------------
	-- MSN Client Interface Procedures
	---------------------------------------------------------------
	/*Synchronizing contacts with notification server, client should call this periodically*/
	procedure synchronize_contacts
	is
		l_trid pls_integer;
	begin
		l_trid := send_cmd_async('SYN', m_contacts_version);
	end;
	
	/*Setting presence status*/
	procedure set_presence_status(status IN varchar2)
	is
		l_trid pls_integer;
	begin
		l_trid := send_cmd_async('CHG', status);
	end;

	/*send ping to server*/
	procedure send_ping
	is
		l_rc pls_integer;
	begin
		l_rc := utl_connection.write_line(m_conn2notify, 'PNG');
		utl_logging.debug('package::plmsn_session.send_ping', 'SENT: PNG');
	end;
	
	procedure add_contact(the_passport IN varchar2, the_gid IN number default 0)
	is
		l_trid pls_integer;
	begin
		l_trid := send_cmd_async('ADD', 'FL ' || the_passport || ' ' || the_passport || ' ' || the_gid);
		l_trid := send_cmd_async('ADD', 'AL ' || the_passport || ' ' || the_passport);
		
		update contacts 
		set	group_id = the_gid,
			display_name = the_passport,
			RL = 'Y'
		where passport=the_passport and owner_id=m_owner_id;
		
		if not SQL%FOUND then
			insert into contacts (passport, display_name, owner_id, group_id, RL)
			values( the_passport, the_passport, m_owner_id, the_gid, 'Y');	
		end if;
		commit;
		utl_logging.info('package::plmsn_session.add_contact', 'ADDED: <' ||the_passport|| '>' );
	end;

	procedure remove_contact(the_passport IN varchar2)
	is
		l_trid pls_integer;
	begin
		l_trid := send_cmd_async('REM', 'FL ' || the_passport );
		l_trid := send_cmd_async('REM', 'AL ' || the_passport );
		
		delete contacts where passport=the_passport and owner_id=m_owner_id;
		commit;
		utl_logging.info('package::plmsn_session.remove_contact', 'REMOVED: <' ||the_passport|| '>' );
	end;
	
	/*change any principal name, me included*/
	procedure rename_contact(the_passport IN varchar2, the_newname IN varchar2)
	is
		l_trid pls_integer;
		l_display_name varchar2(1024) := utl_url.escape(the_newname, false, 'AL32UTF8');
	begin
		l_trid := send_cmd_async('REA', the_passport || ' ' || l_display_name );
		utl_logging.info('package::plmsn_session.remove_contact', 'RENAMED: <' ||the_passport|| '> changed name to [' || the_newname || ']');
	end;
		
	procedure block_contact(the_passport IN varchar2)
	is
		l_trid pls_integer;
	begin
		l_trid := send_cmd_async('REM', 'AL ' || the_passport );
		l_trid := send_cmd_async('ADD', 'BL ' || the_passport );
		utl_logging.info('package::plmsn_session.block_contact', 'BLOCKED: <' ||the_passport|| '>' );
	end;
	
	procedure unblock_contact(the_passport IN varchar2)
	is
		l_trid pls_integer;
	begin
		l_trid := send_cmd_async('REM', 'BL ' || the_passport );
		l_trid := send_cmd_async('ADD', 'AL ' || the_passport );
		utl_logging.info('package::plmsn_session.unblock_contact', 'UNBLOCKED: <' ||the_passport|| '>' );
	end;	
	
	procedure logoff
	is
		l_rc pls_integer;
	begin
		l_rc := utl_connection.write_line(m_conn2notify, 'OUT');
		m_logged_on := false;
		utl_logging.debug('package::plmsn_session.logoff', 'SENT: OUT');
	end;
	
	--Though you can specify a cvs_id here, the conversation may not exist(eg. timedout, get killed)
	--So cvs_id here is just like a hint, the message may not eventually be sent in this conversation (did I say 'this'? ;-) )
	procedure enqueue_message(msg IN varchar2, recipients IN varchar2, cvs_id IN pls_integer default null, msg_opt IN varchar2 default null)
	is
		pragma autonomous_transaction;
		l_trid		pls_integer := null;
		l_cvs_id	pls_integer := null;
	begin
		if cvs_id is not null then
			begin
				select conversation_id into l_cvs_id
				from conversations
				where owner_id = m_owner_id and conversation_id = cvs_id;
				
				--If conversation context is specified, messages should be sent to there, in spite of one2one or one2many
				insert into outgoing_message_queue( msg_id, owner_id, conversation_id, transaction_id, receiver, msg_text, msg_option )
				values (SEQ_OUTGOING_MSG_ID.nextval, m_owner_id, cvs_id, null, recipients, msg, msg_opt);
				
				--to avoid this conversation get timed out
				update conversations set status = 'BUSY' where conversation_id = cvs_id and status = 'IDLE';				
				commit;
				return;
			exception
				--conversation id specified, but does not exist, then move on to the next step
				when NO_DATA_FOUND then
					null;
			end;				
		end if;
		

		begin
			--First, we check if there is an existing one2one conversation with the receiver.
			--NOTE: If the receiver is already in an one2many conversation, this conversation is not used,
			--      because the message to the receiver should be protected from seeing by others
			select conversation_id into l_cvs_id
			from conversations
			where owner_id = m_owner_id and status <> 'CLOSED'
			and (select count(*) from table(ATTENDEES)) = 1 
			and	exists(select COLUMN_VALUE from table(ATTENDEES) where COLUMN_VALUE= recipients);

			insert into outgoing_message_queue( msg_id, owner_id, conversation_id, transaction_id, receiver, msg_text, msg_option )
			values (SEQ_OUTGOING_MSG_ID.nextval, m_owner_id, l_cvs_id, null, recipients, msg, msg_opt);
			
			--to avoid this conversation get timed out
			update conversations set status = 'BUSY' where conversation_id = cvs_id and status = 'IDLE';
		exception
			when NO_DATA_FOUND then
				--If not, check if there is any outgoing message to the same receiver has 
				--already get a conversation context
				begin
					select conversation_id, transaction_id into l_cvs_id, l_trid
					from outgoing_message_queue
					where owner_id = m_owner_id and receiver = recipients and  rownum = 1;
				exception
					when NO_DATA_FOUND then
						--OK then, I must lanuch a new conversation to send the message
						l_trid := send_cmd_async('XFR', 'SB');
						l_cvs_id := null;
				end;

				insert into outgoing_message_queue( msg_id, owner_id, conversation_id, transaction_id, receiver, msg_text, msg_option)
				values (SEQ_OUTGOING_MSG_ID.nextval, m_owner_id, l_cvs_id, l_trid, recipients, msg, msg_opt);
		end;
			
		commit;
	end;

	--This is the public procedure, can be called in different sessions
	--So don't use any packages variables in this procedure
	procedure send_message(owner_id IN number, msg IN varchar2, recipients IN varchar2, cvs_id IN number default null, msg_opt IN varchar2 default 'Default')
	is
		l_owner_passport varchar2(64);
		l_owner_id pls_integer := owner_id;
		l_pipe_ret integer;
		l_resp_txt varchar2(32767);
	begin
		select o.passport into l_owner_passport
		from owners o
		where o.owner_id = l_owner_id;
		
		dbms_pipe.pack_message(item => dbms_pipe.unique_session_name);
		dbms_pipe.pack_message(item => 'SENDMSG');
		dbms_pipe.pack_message(item => recipients);
		dbms_pipe.pack_message(item => msg);
		dbms_pipe.pack_message(item => cvs_id);
		dbms_pipe.pack_message(item => msg_opt);
		
		/*I use owner passport as the pipe name*/
		l_pipe_ret := dbms_pipe.send_message(pipename => l_owner_passport);
		IF l_pipe_ret <> 0 THEN
			utl_logging.error('package::plmsn_session.send_message', 'dbms_pipe.send_message failed: '|| l_pipe_ret);
			return;
		END IF;
		
		--Just purge the information coming from this pipe, whatever is exists or not
		--Cause I don't care this messages
		DBMS_PIPE.PURGE(pipename => dbms_pipe.unique_session_name);   		
		--The message has not been processed yet, so pipe is empty
		--l_pipe_ret := dbms_pipe.receive_message(dbms_pipe.unique_session_name);
		--dbms_pipe.unpack_message(l_resp_txt);
	end;
		
	---------------------------------------------------------------
	-- Dispatch msn owner request coming from pipe
	---------------------------------------------------------------
	procedure dispatch_owner_request
	is
		l_client_pipe varchar2(256);
		l_cmd_txt varchar2(32);
		l_resp_txt varchar2(1024);
		l_status integer;
		l_receiver varchar2(64);
		l_msg varchar2(32767);
		l_cvs_id number;
		l_msg_opt varchar2(64);
	begin
		l_status := dbms_pipe.receive_message(m_passport, 0);
		
		case l_status
			when 0 then -- success
				null; 
			when 1 then -- just timed out, no cmd comes, not a big deal
				return;
			else
				utl_logging.warning('package::plmsn_session.dispatch_owner_request', 'Pipe receive failed: ' || l_status);
				return;
		end case;

		dbms_pipe.unpack_message(l_client_pipe);
		dbms_pipe.unpack_message(l_cmd_txt);
		
		case upper(l_cmd_txt)
			when 'LOGOFF' then
				logoff;
				l_resp_txt := '<'|| m_passport ||'> logged off';
			when 'SENDMSG' then
				dbms_pipe.unpack_message(l_receiver);
				dbms_pipe.unpack_message(l_msg);
				dbms_pipe.unpack_message(l_cvs_id);
				dbms_pipe.unpack_message(l_msg_opt);
				enqueue_message(l_msg, l_receiver, l_cvs_id, l_msg_opt);
				
				if l_cvs_id is not null then
					l_resp_txt := 'Message sent to <' || l_receiver || '> in conversation ' || l_cvs_id;				
				else
					l_resp_txt := 'Message sent to <' || l_receiver || '>';
				end if;
			else
				null;
		end case;
		
		dbms_pipe.pack_message(l_resp_txt);
		l_status := dbms_pipe.send_message(l_client_pipe);
		case l_status
			when 0 then -- success
				null;
			else
				utl_logging.warning('package::plmsn_session.dispatch_owner_request', 'Pipe send failed: ' || l_status);
				return;
		end case;
	
	end;
	
	---------------------------------------------------------------
	-- MSN Notification Handlers
	---------------------------------------------------------------	 
	procedure handle_CHL(args IN string_array)
	is
		l_payload	varchar2(32767) := null;
		l_trid		pls_integer;
	begin
		--client id:msmsgs@msnmsgr.com	maps to client string: Q1P7W2E4J9R8U3S5
		l_payload := utl_raw.cast_to_raw(  DBMS_OBFUSCATION_TOOLKIT.MD5(input_string => args(3) || 'Q1P7W2E4J9R8U3S5') );
		l_trid := send_cmd_async('QRY', 'msmsgs@msnmsgr.com 32', l_payload);
	end;
	
	procedure handle_SYN(args IN string_array)
	is
	begin
		m_contacts_version := args(3);
	end;

	procedure handle_GTC(args IN string_array)
	is
	begin
		null;
	end;

	procedure handle_BLP(args IN string_array)
	is
	begin
		null;
	end;
	
	procedure handle_PRP(args IN string_array)
	is
	begin
		null;
	end;
					
	procedure handle_LSG(args IN string_array)
	is
	begin
		update groups set group_name = args(3) where owner_id = m_owner_id and group_id = args(2);
						
		if not SQL%FOUND then
			insert into groups (owner_id, group_id, group_name) values (m_owner_id, args(2), args(3));
		end if;
		commit;	
	end;
	
	procedure handle_LST(args IN string_array)
	is
		l_argc pls_integer;
		l_gid pls_integer := null;
		l_display_name varchar2(1024) := utl_url.unescape(args(3), 'AL32UTF8');
	begin
		l_argc := args.COUNT();
		if l_argc >=5 then
			l_gid := utl_string.split(args(5),',')(1);
		end if;
		
		update contacts set
			display_name = l_display_name,
			status = null,
			group_id = l_gid,
			FL = (case bitand(args(4), 1) when 0 then 'N' else 'Y' end),
			AL = (case bitand(args(4), 2) when 0 then 'N' else 'Y' end),
			BL = (case bitand(args(4), 4) when 0 then 'N' else 'Y' end),
			RL = (case bitand(args(4), 8) when 0 then 'N' else 'Y' end),
			PL = (case bitand(args(4), 16) when 0 then 'N' else 'Y' end)
		where passport=args(2) and owner_id=m_owner_id;
		
		if not SQL%FOUND then
			insert into contacts (owner_id, passport, display_name, display_msg, status, group_id, FL, AL, BL, RL, PL)
			values( m_owner_id, args(2), l_display_name, null, null, l_gid,
					(case bitand(args(4), 1) when 0 then 'N' else 'Y' end),
					(case bitand(args(4), 2) when 0 then 'N' else 'Y' end),
					(case bitand(args(4), 4) when 0 then 'N' else 'Y' end),
					(case bitand(args(4), 8) when 0 then 'N' else 'Y' end),
					(case bitand(args(4), 16) when 0 then 'N' else 'Y' end) );	
		end if;
		commit;
	end;

	procedure handle_MSG(args IN string_array)
	is
		l_msg_len pls_integer := args(4);
		l_msg raw(32767);
	begin
		l_msg := utl_connection.get_raw(m_conn2notify, l_msg_len);
		utl_logging.debug('package::plmsn_session.handle_MSG', 'MESSAGE: ' || utl_raw.cast_to_varchar2(l_msg) );
	end;
	
	procedure handle_NOT(args IN string_array)
	is
	begin
		null;
	end;
	
	procedure handle_QNG(args IN string_array)
	is
	begin
		--get a ping response from server
		m_last_response := dbms_utility.get_time();
	end;
	
	procedure handle_OUT(args IN string_array)
	is
	begin
		m_logged_on := false;
	end;
	
	--Oh yeah, someone is calling me...
	procedure handle_RNG(args IN string_array)
	is
		l_session_id pls_integer := args(2);
		l_switchboard varchar2(32) := args(3);
		l_authentication varchar2(64) := args(5);
		l_inviter varchar2(64) := args(6);
		l_conversation_id pls_integer;
	begin
		select seq_conversation_id.nextval into l_conversation_id from dual;
		insert into conversations ( conversation_id, owner_id, inviter, session_id, authentication, switchboard, status, attendees)
		values ( l_conversation_id, m_owner_id, l_inviter, l_session_id, l_authentication, l_switchboard, 'ISSUED', attendees_t());
		commit;
		plmsn_events.OnInvited(l_inviter, l_conversation_id);
		utl_logging.info('package::plmsn_session.handle_RNG', '<' || l_inviter || '> invites you to a conversation ');
	end;
	
	procedure handle_CHG(args IN string_array)
	is
		l_status varchar2(3) := args(3);
	begin
		update owners
		set status = l_status
		where owner_id = m_owner_id;
		commit;
	end;
	
	procedure handle_ILN(args IN string_array)
	is
		l_display_name varchar2(1024) := utl_url.unescape(args(5), 'AL32UTF8');
	begin
		update contacts set
			display_name = l_display_name,
			status = args(3)
		where passport=args(4) and owner_id=m_owner_id;
		commit;
	end;
	
	procedure handle_NLN(args IN string_array)
	is
		l_display_name varchar2(1024) := utl_url.unescape(args(4), 'AL32UTF8');
	begin
		update contacts set
			display_name = l_display_name,
			status = args(2)
		where passport=args(3) and owner_id=m_owner_id;
		commit;
	end;
	
	procedure handle_FLN(args IN string_array)
	is
	begin
		update contacts 
		set status = 'FLN'
		where passport=args(2) and owner_id=m_owner_id;
		commit;
		--since the principle has left, just drop all his service instances
		plmsn_services.stop_service(m_owner_id, args(2));
	end;
	
	procedure handle_XFR(args IN string_array)
	is
		l_trid pls_integer := args(2);
		l_server_type varchar2(8) := args(3);
		l_switchboard varchar2(32) := args(4);
		l_authentication varchar2(64) := args(6);
		l_cvs_id pls_integer;
		l_initial_attendee varchar2(64);
		l_host varchar2(256);
		l_port pls_integer;
	begin
		if l_server_type = 'SB' then
			select seq_conversation_id.nextval into l_cvs_id from dual;

			select receiver into l_initial_attendee
			from outgoing_message_queue
			where transaction_id = l_trid and rownum = 1;

			insert into conversations ( conversation_id, owner_id, inviter, session_id, authentication, switchboard, status, attendees, initial_attendee)
			values ( l_cvs_id, m_owner_id, m_passport, null, l_authentication, l_switchboard, 'ISSUED', attendees_t(), l_initial_attendee);
			
			--set the destination of messages on the waiting queue
			update outgoing_message_queue
			set conversation_id = l_cvs_id
			where transaction_id = l_trid;

			commit;
			utl_logging.info('package::plmsn_session.handle_XFR', '<' || m_passport || '> launched a conversation ');
		else -- NS
			utl_logging.info('package::plmsn_session.handle_XFR', 'Notification server overloaded, redirecting to ' || args(4));
			l_host := utl_string.split(args(4),':')(1);
			l_port := utl_string.split(args(4),':')(2);
			utl_connection.close_connection(m_conn2notify);
			m_conn2notify := utl_connection.open_connection(l_host, l_port, m_http_proxy);			
		end if;
	end;
	
	procedure handle_ADG(args IN string_array)
	is
	begin
		null;
	end;
	
	procedure handle_RMG(args IN string_array)
	is
	begin
		null;
	end;
	
	procedure handle_REG(args IN string_array)
	is
	begin
		null;
	end;
	
	procedure handle_ADD(args IN string_array)
	is
		l_list varchar2(2) := args(3);
		l_contacts_ver pls_integer := args(4);
		l_passport varchar2(64) := args(5);
		l_display_name varchar2(1024) := args(6);
	begin
		--ADD 0 RL 0 maggie_jipeixin@126.com maggie_jipeixin@126.com%20(E-mail%20Address%20Not%20Verified)
		case l_list
			when 'RL' then -- someone has added me to his/her FL
				utl_logging.info('package::plmsn_session.handle_ADD', 'ADDED BY: <'||l_passport||'> '||l_display_name);
				add_contact(l_passport);
			when 'AL' then
				m_contacts_version := l_contacts_ver;
				update contacts set AL = 'Y'
				where passport=l_passport and owner_id=m_owner_id;
				commit;				
			when 'BL' then
				m_contacts_version := l_contacts_ver;
				update contacts set BL = 'Y'
				where passport=l_passport and owner_id=m_owner_id;	
				commit;
			when 'FL' then
				m_contacts_version := l_contacts_ver;
				update contacts set FL = 'Y'
				where passport=l_passport and owner_id=m_owner_id;
				commit;
			else 
				null;				
		end case;
	end;
	
	procedure handle_REM(args IN string_array)
	is
		l_list varchar2(2) := args(3);
		l_contacts_ver pls_integer := args(4);
		l_passport varchar2(64) := args(5);
	begin
		--REM 0 RL 0 coolmandjt@hotmail.com
		case l_list
			when 'RL' then -- someone has remove me from his/her FL
				utl_logging.info('package::plmsn_session.handle_REM', 'REMOVED BY: <'||l_passport||'>');
				remove_contact(l_passport);
			when 'AL' then
				m_contacts_version := l_contacts_ver;	
			when 'BL' then
				m_contacts_version := l_contacts_ver;
			when 'FL' then
				m_contacts_version := l_contacts_ver;
			else 
				null;				
		end case;
	end;
	
	procedure handle_REA(args IN string_array)
	is
		l_contacts_ver pls_integer := args(3);
		l_passport varchar2(64) := args(4);
		l_new_display_name varchar2(1024) := utl_url.unescape(args(5), 'AL32UTF8');
	begin
		--REA 103 3055 mypassport@passport.com new%20name\r\n
		m_contacts_version := l_contacts_ver;
		
		update contacts
		set display_name = l_new_display_name
		where passport=l_passport and owner_id=m_owner_id;
		
		commit;
	end;
	
	procedure handle_ADC(args IN string_array)
	is
	begin
		null;
	end;

	---------------------------------------------------------------
	-- Notification dispatching
	---------------------------------------------------------------	 	
	/*Dispatch notification to it's handling procedures*/
	procedure dispatch_notification(cmd_buf IN varchar2)
	is
		args string_array := string_array();
	begin
		args := utl_string.split(cmd_buf);
		
		case args(1)
			when 'CHL' then
				handle_CHL(args);
			when 'SYN' then
				handle_SYN(args);
			when 'GTC' then
				handle_GTC(args);
			when 'BLP' then
				handle_BLP(args);
			when 'PRP' then
				handle_PRP(args);
			when 'LSG' then
				handle_LSG(args);
			when 'LST' then
				handle_LST(args);
			when 'MSG' then
				handle_MSG(args);
			when 'NOT' then
				handle_NOT(args);
			when 'QNG' then
				handle_QNG(args);
			when 'OUT' then
				handle_OUT(args);
			when 'RNG' then
				handle_RNG(args);
			when 'CHG' then
				handle_CHG(args);
			when 'ILN' then
				handle_ILN(args);
			when 'NLN' then
				handle_NLN(args);
			when 'FLN' then
				handle_FLN(args);
			when 'XFR' then
				handle_XFR(args);
			when 'ADG' then
				handle_ADG(args);
			when 'RMG' then
				handle_RMG(args);
			when 'REG' then
				handle_REG(args);
			when 'ADD' then
				handle_ADD(args);
			when 'REM' then
				handle_REM(args);
			when 'REA' then
				handle_REA(args);
			when 'ADC' then
				handle_ADC(args);	
			else
--				if regexp_instr(args(1), '[0-9]+') <> 0 then
--					utl_logging.error('package::plmsn_session.dispatch_notification', 'MSN ERROR: ' || args(1) );
--					m_logged_on := false;
--				end if;
				null;
		end case;
		
		args.delete;
	end;
	
	
	---------------------------------------------------------------
	-- Public member procedures and functions
	---------------------------------------------------------------
		
	procedure logon(owner_id IN pls_integer, passport IN varchar2, password IN varchar2)
	is
		l_tweener varchar2(32767);
		l_ticket varchar2(32767);
		l_resp_text varchar2(32767);
		l_display_name varchar2(1024);
	begin		
		m_passport := passport;
		m_password := password;
		m_owner_id := owner_id;
		
		utl_logging.set_session_id(m_owner_id);
		
		l_tweener := get_tweener();
		l_ticket := get_ticket(l_tweener);

		l_resp_text := send_cmd_sync('USR', 'TWN S ' || l_ticket);
		--USR 6 OK james_deng_jt@msn.com doodle 1 0
		if regexp_instr(l_resp_text, 'USR [0-9]+ OK ' || m_passport) <> 0 
		then
			m_logged_on := true;
			utl_logging.info('package::plmsn_session.logon', 'LOGON SUCCEEDED: ' || l_resp_text );
		else
			m_logged_on := false;
			utl_logging.error('package::plmsn_session.logon', 'LOGON FAILED: ' || l_resp_text );
			return;
		end if;
		
		--Synchronizing contact lists
		synchronize_contacts;
			
		--Setting initial presence
		set_presence_status(m_initial_status);
		
		--Setting the display name
		select display_name into l_display_name 
		from owners
		where owner_id = m_owner_id;
		rename_contact(m_passport, l_display_name);
		
		m_last_response := dbms_utility.get_time();
	end;

	/*The main processing engine for notification server*/
	procedure process
	is
		l_buffer	varchar2(32767);
		l_last_ping	pls_integer :=0;
		l_last_sync pls_integer :=0;
	begin
		--Initializing packages
		plmsn_conversation.set_owner_id(m_owner_id);
		plmsn_conversation.cleanup;
		plmsn_events.set_owner_id(m_owner_id);
		DBMS_PIPE.PURGE(m_passport);
		
		while m_logged_on
		loop
			--process traffic on notification connection, 
			--wait some time here can reduce CPU usage dramatically!!!
			if utl_connection.available(m_conn2notify, 1) > 0
			then
				begin
					l_buffer := utl_connection.get_line(m_conn2notify, true);
					utl_logging.debug('package::plmsn_session.process', 'RECV: ' || l_buffer);
					if l_buffer is not null
					then
						dispatch_notification(l_buffer);
					end if;	
				exception
					when utl_connection.END_OF_INPUT or utl_connection.NETWORK_ERROR then
						utl_logging.error('package::plmsn_session.process', 'Lost connection to notification server: ' || m_crlf || SQLERRM || m_crlf || DBMS_UTILITY.format_error_backtrace);
						m_logged_on := false;
				end;
			end if;
			
			--dispatch owner request
			dispatch_owner_request;
			
			--I could have received OUT from NS, or a network exception, so I have to stop here
			exit when not m_logged_on;
			
			--process conversations here
			plmsn_conversation.process;
			
			--send ping at an interval >= 30 seconds
			if (dbms_utility.get_time()-l_last_ping) >= 30*100 then
				send_ping;
				l_last_ping := dbms_utility.get_time();
			end if;
			
			--if ping is timed out after 60 seconds, we must log off 
			if (dbms_utility.get_time()-m_last_response) >= 90*100 then
				utl_logging.error('package::plmsn_session.process', 'Notification server does not response in 90 seconds, force logging off.');
				m_logged_on := false;
			end if;
							
		end loop;
		
		--Release all resources
		plmsn_conversation.cleanup;
		utl_connection.close_connection(m_conn2notify);
		utl_connection.close_all_connections();
		DBMS_PIPE.PURGE(m_passport);		
	exception
		when others then
			utl_logging.error('package::plmsn_session.process', 'PLMSN_SESSION.PROCESS EXITS ABNORMALLY: ' || m_crlf || SQLERRM || m_crlf || DBMS_UTILITY.format_error_backtrace );
			--Release all resources
			plmsn_conversation.cleanup;
			utl_connection.close_all_connections();
			DBMS_PIPE.PURGE(m_passport);
	end;
	
	
	---------------------------------------------------------------
	-- Package Initialization
	---------------------------------------------------------------
--begin
--	utl_logging.set_logging_level(UTL_LOGGING.DEBUG_LEVEL);	
end;
/
show err;
